package com.example.myapplication;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseHelper extends SQLiteOpenHelper {
    private static final String DATABASE_NAME = "money_database.db";
    private static final int DATABASE_VERSION = 12;

    public static final String TABLE_NAME = "money_records";
    public static final String COLUMN_ID = "id";
    public static final String COLUMN_DATE = "date";
    public static final String COLUMN_INCOME = "income";
    public static final String COLUMN_INCOME_CATEGORY = "income_category";
    public static final String COLUMN_WASTE = "waste";
    public static final String COLUMN_WASTE_CATEGORY = "waste_category";
    public static final String COLUMN_BALANCE = "balance";

    public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String createTable = "CREATE TABLE " + TABLE_NAME + " (" +
                COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                COLUMN_DATE + " TEXT, " +
                COLUMN_INCOME + " REAL, " +
                COLUMN_INCOME_CATEGORY + " TEXT, " + // 新增
                COLUMN_WASTE + " REAL, " +
                COLUMN_WASTE_CATEGORY + " TEXT, " + // 新增
                COLUMN_BALANCE + " REAL);";
        db.execSQL(createTable);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        if (oldVersion < 12) {
            db.execSQL("ALTER TABLE " + TABLE_NAME + " ADD COLUMN " + COLUMN_INCOME_CATEGORY + " TEXT DEFAULT '工资'");
            db.execSQL("ALTER TABLE " + TABLE_NAME + " ADD COLUMN " + COLUMN_WASTE_CATEGORY + " TEXT DEFAULT '餐饮'");
        }
    }

    public double getLatestBalance() {
        SQLiteDatabase db = this.getReadableDatabase();
        double latestBalance = 0.0;

        Cursor cursor = db.rawQuery(
                "SELECT " + COLUMN_BALANCE +
                        " FROM " + TABLE_NAME +
                        " ORDER BY " + COLUMN_ID + " DESC LIMIT 1",
                null
        );

        if (cursor != null && cursor.moveToFirst()) {
            int balanceIndex = cursor.getColumnIndex(COLUMN_BALANCE);
            if (balanceIndex >= 0) {
                latestBalance = cursor.getDouble(balanceIndex);
            }
            cursor.close();
        }
        return latestBalance;
    }

    public void addRecord(String date, double income, String incomeCategory,
                          double waste, String wasteCategory, double balance) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(COLUMN_DATE, date);
        values.put(COLUMN_INCOME, income);
        values.put(COLUMN_INCOME_CATEGORY, incomeCategory);
        values.put(COLUMN_WASTE, waste);
        values.put(COLUMN_WASTE_CATEGORY, wasteCategory);
        values.put(COLUMN_BALANCE, balance);
        db.insert(TABLE_NAME, null, values);
        db.close();
    }

    public Cursor getAllRecords() {
        SQLiteDatabase db = this.getReadableDatabase();
        return db.rawQuery("SELECT * FROM " + TABLE_NAME + " ORDER BY " + COLUMN_ID + " DESC", null);
    }

    public Cursor getIncomeRecords() {
        SQLiteDatabase db = this.getReadableDatabase();
        return db.rawQuery("SELECT * FROM " + TABLE_NAME +
                " WHERE " + COLUMN_INCOME + " > 0 " +
                " ORDER BY " + COLUMN_ID + " DESC", null);
    }

    public Cursor getExpenseRecords() {
        SQLiteDatabase db = this.getReadableDatabase();
        return db.rawQuery("SELECT * FROM " + TABLE_NAME +
                " WHERE " + COLUMN_WASTE + " > 0 " +
                " ORDER BY " + COLUMN_ID + " DESC", null);
    }
    // 在DatabaseHelper类中添加以下方法

    public Cursor getIncomeGroupedByCategory() {
        SQLiteDatabase db = this.getReadableDatabase();
        return db.rawQuery("SELECT " + COLUMN_INCOME_CATEGORY + ", " +
                "SUM(" + COLUMN_INCOME + ") AS total_income, " +
                "COUNT(*) AS count " +
                "FROM " + TABLE_NAME +
                " WHERE " + COLUMN_INCOME + " > 0 " +
                "GROUP BY " + COLUMN_INCOME_CATEGORY +
                " ORDER BY total_income DESC", null);
    }

    public Cursor getExpenseGroupedByCategory() {
        SQLiteDatabase db = this.getReadableDatabase();
        return db.rawQuery("SELECT " + COLUMN_WASTE_CATEGORY + ", " +
                "SUM(" + COLUMN_WASTE + ") AS total_expense, " +
                "COUNT(*) AS count " +
                "FROM " + TABLE_NAME +
                " WHERE " + COLUMN_WASTE + " > 0 " +
                "GROUP BY " + COLUMN_WASTE_CATEGORY +
                " ORDER BY total_expense DESC", null);
    }

    public Cursor getRecordsByIncomeCategory(String category) {
        SQLiteDatabase db = this.getReadableDatabase();
        return db.rawQuery("SELECT * FROM " + TABLE_NAME +
                " WHERE " + COLUMN_INCOME_CATEGORY + " = ? " +
                " ORDER BY " + COLUMN_ID + " DESC", new String[]{category});
    }

    public Cursor getRecordsByExpenseCategory(String category) {
        SQLiteDatabase db = this.getReadableDatabase();
        return db.rawQuery("SELECT * FROM " + TABLE_NAME +
                " WHERE " + COLUMN_WASTE_CATEGORY + " = ? " +
                " ORDER BY " + COLUMN_ID + " DESC", new String[]{category});
    }
}